package com.gwclh.personnel.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.gwclh.personnel.entity.DeptTransfer;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.gwclh.personnel.vo.transfervo.DeptTransFerVo;

import java.util.List;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 部门调动记录表 Mapper 接口
 * </p>
 *
 * @author gzhhh
 * @since 2020-07-24
 */
public interface DeptTransferMapper extends BaseMapper<DeptTransfer> {

    @Select("SELECT DATE_FORMAT(create_time,'%Y-%m') as time,count(*) as total from t_dept_transfer WHERE DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') GROUP BY time")
    Map<String,Object> statisticsDeptTransferMonth();

    @Select("SELECt d.dept_name as name," +
            "COUNT(IF(MONTH(dt.create_time)= 1,TRUE,NULL)) AS '1'," +
            "COUNT(IF(MONTH(dt.create_time)= 2,TRUE,NULL)) AS '2'," +
            "COUNT(IF(MONTH(dt.create_time)= 3,TRUE,NULL)) AS '3'," +
            "COUNT(IF(MONTH(dt.create_time)= 4,TRUE,NULL)) AS '4'," +
            "COUNT(IF(MONTH(dt.create_time)= 5,TRUE,NULL)) AS '5'," +
            "COUNT(IF(MONTH(dt.create_time)= 6,TRUE,NULL)) AS '6'," +
            "COUNT(IF(MONTH(dt.create_time)= 7,TRUE,NULL)) AS '7'," +
            "COUNT(IF(MONTH(dt.create_time)= 8,TRUE,NULL)) AS '8'," +
            "COUNT(IF(MONTH(dt.create_time)= 9,TRUE,NULL)) AS '9'," +
            "COUNT(IF(MONTH(dt.create_time)=10,TRUE,NULL)) AS '10'," +
            "COUNT(IF(MONTH(dt.create_time)=11,TRUE,NULL)) AS '11'," +
            "COUNT(IF(MONTH(dt.create_time)=12,TRUE,NULL)) AS '12'" +
            "FROM t_dept_transfer dt, t_department d " +
            "WHERE dt.pre_dept_id = d.id " +
            "AND d.p_id = #{pId} " +
            "GROUP BY dt.pre_dept_id")
    List<Map<String,Object>> selectDeptTransDate(Integer pId);
}
